<?php

class StatisticsController extends CController
{
	const PAGE_SIZE=10;

	/**
	 * @var string specifies the default action to be 'list'.
	 */
	public $defaultAction='truck_exam';

	/**
	 * @var CActiveRecord the currently loaded data model instance.
	 */
	private $_model;
	
	/**
	 * @return array action filters
	 */
	public function filters()
	{
		return array(
			array('application.filters.LoginFilter'),
			array('application.filters.AuthFilter'),
		);
	}

	public function actionTruck_exam(){//车辆考核汇总
		$criteria=new CDbCriteria();
		$criteria->addCondition('state=0');
		if(isset($_POST['time_mode'])) $_GET['time_mode']=$_POST['time_mode'];
		if(!isset($_GET['time_mode'])) $_GET['time_mode']=1;
		if($_GET['time_mode']==0){//按天查询  取消 'A' as merit_pay_kind,档次
			$criteria->select="plate_no,main_driver_name,type_name, sum(to_income) as to_income,sum(salary) as salary,sum(gas_fee) as gas_fee,sum(bridge_charge) as bridge_charge,
			sum(maintain_fee) as maintain_fee,sum(repair_fee) as repair_fee,sum(amerce_fee) as amerce_fee,sum(reparation) as reparation,sum(other_fee) as other_fee,
			sum(depreciation) as depreciation,sum(insure_fee) as insure_fee,sum(managing_cost) as managing_cost,sum(salary_cost) as salary_cost,
			sum(gps_fee) as gps_fee,sum(root_cost) as root_cost,sum(total_cost) as total_cost,sum(profit) as profit,sum(merit_pay) as merit_pay,
			'100%' as merit_pay_ratio,sum(merit_pay) as actual_salary,sum(mm_cost) as mm_cost,sum(avi_cost) as avi_cost,sum(gi_cost) as gi_cost,truck_kind,
			CONCAT(FORMAT(sum(gas_fee)/sum(to_income)*100,2),'%') as gas_ratio,CONCAT(format(sum(bridge_charge)/sum(to_income)*100,2),'%') as bridge_ratio,
			CONCAT(format(sum(repair_fee)/sum(to_income)*100,2),'%') as rapair_ratio,CONCAT(format(sum(amerce_fee)/sum(to_income)*100,2),'%') as amerce_ratio,
			CONCAT(format(sum(other_fee)/sum(to_income)*100,2),'%') as other_ratio,CONCAT(format(sum(profit)/sum(to_income)*100,2),'%') as profit_ratio,stat_date";
			$criteria->addCondition('stat_region=0');
			$criteria->group='plate_no';
		}
		else{//按月查询  取消 merit_pay_kind 档次
			$criteria->select="plate_no,main_driver_name,type_name, to_income,salary,gas_fee,bridge_charge,
			maintain_fee,repair_fee,amerce_fee,reparation,other_fee,mm_cost,avi_cost,gi_cost,
			depreciation,insure_fee,managing_cost,salary_cost,
			gps_fee,root_cost,total_cost,profit,merit_pay,merit_pay_kind,
			CONCAT(FORMAT(merit_pay_ratio*100,0),'%') as merit_pay_ratio,actual_salary,truck_kind,
			CONCAT(FORMAT(gas_ratio*100,2),'%') as gas_ratio,CONCAT(format(bridge_ratio*100,2),'%') as bridge_ratio,
			CONCAT(format(rapair_ratio*100,2),'%') as rapair_ratio,CONCAT(format(amerce_ratio*100,2),'%') as amerce_ratio,
			CONCAT(format(other_ratio*100,2),'%') as other_ratio,CONCAT(format(profit_ratio*100,2),'%') as profit_ratio,
			DATE_FORMAT(stat_date,'%Y-%m') as stat_date";
			$criteria->addCondition('stat_region=1');
		}
		$blur=true;
		if(Yii::app()->getRequest()->isPostRequest){
			if(isset($_POST['keys'])){//页面传来的搜索关键字数组
				$_GET['keys']=$_POST['keys'];
			}
		}
		if(isset($_GET['keys'])){
			if(isset($_GET['keys']['blur'])&&$_GET['keys']['blur']=='0') $blur=false;
			foreach($_GET['keys'] as $key=>$val){
				if(trim($val)=='')
					unset($_GET['keys'][$key]);
				else if($key=='s_date'){//时间查询
					if($_GET['time_mode']==0)
						$criteria->addCondition("stat_date>='{$val}'");
					else
						$criteria->addCondition("stat_date='{$val}-01'");	
				}
				else if($key=='e_date'){//时间查询  
					if($_GET['time_mode']==0)
						$criteria->addCondition("stat_date<'".date('Y-m-d',strtotime($val." +1days"))."'");
				}	
				else if($key!='blur'){//按照模糊或精确查询
					$blur?$criteria->addSearchCondition($key,$val):$criteria->addCondition("{$key}='{$val}'");
				}
			}
		}
		
		if(isset($_GET['excel']))//输出excel
			$this->toTruckExamExcel($criteria);
		else	//转入查询/修改页面
			$this->toTruckExamPage($criteria);
		
	}
	
	public function toTruckExamPage($criteria){
		$model=Truck_exam::model();
		if($_GET['time_mode']==0){//按天查询
			$command=$model->getCommandBuilder();//因group by 查询准确记录数量
			$sql="select count(t.id) from( select id FROM truck_exam";
			$sql=$command->applyCondition($sql,$criteria->condition);
			$sql=$command->applyGroup($sql,$criteria->group).") as t";//echo $sql;
			$pages=new CPagination($model->countBySql($sql,$criteria->params));
			$pages->pageSize=Yii::app()->params['page_size']['truck_exam'];
			$pages->applyLimit($criteria);
		
			$sort=new CSort('Truck_exam');
			$sort->defaultOrder='main_driver_name desc';
			$sort->applyOrder($criteria);
			$sql='SELECT '.$criteria->select.' FROM (select * from truck_exam order by stat_date desc) as t';//读取最后一个统计时间
			$sql=$command->applyCondition($sql,$criteria->condition);
			$sql=$command->applyGroup($sql,$criteria->group);//echo $sql;
			$sql=$command->applyOrder($sql,$criteria->order);
			$sql=$command->applyLimit($sql,$criteria->limit,$criteria->offset);
			$models=$model->findAllBySql($sql,$criteria->params);
		}
		else{//按月查询
			$pages=new CPagination($model->count($criteria));
			$pages->pageSize=Yii::app()->params['page_size']['truck_exam'];
			$pages->applyLimit($criteria);
		
			$sort=new CSort('Truck_exam');
			$sort->defaultOrder='main_driver_name desc';
			$sort->applyOrder($criteria);
			$models=$model->findAll($criteria);
		}
		//如果最后一页加合计
		if(($pages->getCurrentPage()+1)==$pages->getPageCount()){
			$attr_names=$model->excelAttributes();
			$sql="'&nbsp;&nbsp;&nbsp;&nbsp;合&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;计' as plate_no";
			foreach($attr_names as $attr=>$val){
				if(isset($val['sum'])){
					if(($val['sum'])==1)
						$sql.=",sum($attr) as $attr";
					else
						$sql.=",".$val['sum'].' as '.$attr;
				}
			}
			$criteria->select=$sql;
			$criteria->order='';
			$criteria->limit=1;$criteria->offset=-1;
			$sum_model=$model->find($criteria);
			$models[]=$sum_model;
		}
		$this->render('truck_exam',array(
			'models'=>$models,
			'pages'=>$pages,
			'sort'=>$sort,
		));
	}
	
	public function toTruckExamExcel($criteria){
		$model=Truck_exam::model();
		if($_GET['time_mode']==0){//按天查询
			$sort=new CSort('Truck_exam');
			$sort->defaultOrder='main_driver_name desc';
			$sort->applyOrder($criteria);
			$command=$model->getCommandBuilder();//读取最后一个统计时间
			$sql='SELECT '.$criteria->select.' FROM (select * from truck_exam order by stat_date desc) as t';
			$sql=$command->applyCondition($sql,$criteria->condition);
			$sql=$command->applyGroup($sql,$criteria->group);//echo $sql;
			$sql=$command->applyOrder($sql,$criteria->order);
			$sql=$command->applyLimit($sql,$criteria->limit,$criteria->offset);
			$models=$model->findAllBySql($sql,$criteria->params);
		}else{
			$sort=new CSort('Truck_exam');
			$sort->defaultOrder='main_driver_name desc';
			$sort->applyOrder($criteria);
			$models=$model->findAll($criteria);			
		}
		
		$atrr_names=$model->attributeLabels();
		$excel_names=$model->excelAttributes();$end_cell=end($excel_names);
		$tab_name='车辆考核汇总 ';
		$tab_name.=(isset($_GET['keys']['s_date'])?$_GET['keys']['s_date']:'');
		if($_GET['time_mode']==0)
			$tab_name.=(isset($_GET['keys']['e_date'])?'至'.$_GET['keys']['e_date']:'');
		$tab_name=trim($tab_name);	
		
		require_once './protected/extensions/excel/PHPExcel.php';
		require_once './protected/extensions/excel/PHPExcel/IOFactory.php';
		$objPHPExcel = new PHPExcel();
		
		//所有单元格添加边框
		$sum=count($models);
		$sharedStyle = new PHPExcel_Style();
		$sharedStyle->applyFromArray(
		array('borders' => array(
			'left'	=> array('style' => PHPExcel_Style_Border::BORDER_THIN),
			'top'	=> array('style' => PHPExcel_Style_Border::BORDER_THIN),
			'bottom'	=> array('style' => PHPExcel_Style_Border::BORDER_THIN),
			'right'		=> array('style' => PHPExcel_Style_Border::BORDER_THIN)
			)
		 ));
		 $objPHPExcel->getActiveSheet()->setSharedStyle($sharedStyle, "A1:AJ".($sum+4));
		//开始设置标题
		$objPHPExcel->setActiveSheetIndex(0)->setTitle($tab_name);
		$objPHPExcel->getActiveSheet()->mergeCells('A1:'.$end_cell[0].'1');
		$objPHPExcel->getActiveSheet()->setCellValue('A1',$tab_name);
		$objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setSize(15);
		$objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setHorizontal('center');
		$objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(5);
		$objPHPExcel->getActiveSheet()->setCellValue('A2','序号');
		$objPHPExcel->getActiveSheet()->getStyle('A2')->getAlignment()->setVertical('center')->setHorizontal('center');
		$objPHPExcel->getActiveSheet()->mergeCells('A2:A3');
		$objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(5);
		$objPHPExcel->getActiveSheet()->freezePane('F4');//标题栏 冻结窗口
		foreach($excel_names as $attr=>$val){//创建标题
			$row=3;
			if(isset($val['merge'])&&$val['merge']){
				$objPHPExcel->getActiveSheet()->mergeCells($val[0].'2:'.$val[0].'3');
				$objPHPExcel->getActiveSheet()->getStyle($val[0].'2')->getAlignment()->setVertical('center')->setHorizontal('center');
				$row=2;
			}
			$objPHPExcel->getActiveSheet()->setCellValue($val[0].$row,$atrr_names[$attr]);
			$objPHPExcel->getActiveSheet()->getColumnDimension($val[0])->setWidth($val[1]);
		}
		//合并F2:M2标题 日常费用 N2:U2标题 固定费用
		$objPHPExcel->getActiveSheet()->mergeCells('F2:M2');
		$objPHPExcel->getActiveSheet()->setCellValue('F2','日常费用');
		$objPHPExcel->getActiveSheet()->getStyle('F2')->getAlignment()->setHorizontal('center');
		$objPHPExcel->getActiveSheet()->mergeCells('N2:U2');
		$objPHPExcel->getActiveSheet()->setCellValue('N2','固定费用');
		$objPHPExcel->getActiveSheet()->getStyle('N2')->getAlignment()->setHorizontal('center');
		
		$row_index=4;//行索引 数据从3开始 例如A从A3开始添加数据
		foreach($models as $n=>$model){
			$objPHPExcel->getActiveSheet()->setCellValue('A'.($n+$row_index),$n+1);
			foreach($excel_names as $attr=>$val){//双循环，根据model内设置进行数据导出
				$value=$model->$attr;
				if(isset($val[2]))//值->字符串 转换
					$value=$model->$val[2]();
				$objPHPExcel->getActiveSheet()->setCellValue($val[0].($n+$row_index),$value);
			}
		}
		if(!empty($models)){//最后一行后 合计
			$objPHPExcel->getActiveSheet()->setCellValue('A'.($n+$row_index+1),$n+2);
			$objPHPExcel->getActiveSheet()->setCellValue('B'.($n+$row_index+1),'合      计');
			foreach($excel_names as $attr=>$val){
				if(isset($val['sum'])){
					if($val['sum']==1){
						$sum="=sum($val[0]$row_index:$val[0]".($row_index+$n).")";//例：=sum(E3:E7)
						$objPHPExcel->getActiveSheet()->setCellValue($val[0].($n+$row_index+1),$sum);
					}
					else{
						$cols=str_ireplace('%',($n+$row_index+1),$val['sum_excel']);
						$sum='=CONCATENATE(round('.$cols.'*100,2),"%")';
						$objPHPExcel->getActiveSheet()->setCellValue($val[0].($n+$row_index+1),$sum);
					}
				}
			}
		}
		//隐藏固定费用明细
		$hide_cell=array('N','O','P','Q','R','S','T','U');
		foreach($hide_cell as $val)
			$objPHPExcel->getActiveSheet()->getColumnDimension($val)->setVisible(false);
		
		header('Content-Type: application/vnd.ms-excel');
		header('Content-Disposition: attachment;filename='.iconv("utf-8", "gb2312", $tab_name.'.xls'));
		header('Cache-Control: max-age=0');
		$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
		$objWriter->save('php://output'); 
	}
	
	public function actionSingle_truck_exam(){
		$criteria=new CDbCriteria();
		$criteria->select='confirm_sheet.confirm_no,date_format(confirm_sheet.appointment_time,"%Y-%m-%d") as appointment_time,
			date_format(confirm_sheet.create_time,"%Y-%m-%d") as create_time,
			confirm_sheet.truck_plate_no,if(confirm_sheet.driver2_name="",confirm_sheet.driver1_name,concat(confirm_sheet.driver1_name,",",confirm_sheet.driver2_name)) as driver_name,
  			confirm_goods_info.consignor_name, confirm_sheet.origin,confirm_sheet.destination,
  			confirm_sheet.total_amount,confirm_sheet.return_paper,confirm_sheet.driver_salary,confirm_sheet.gas_fee,
  			confirm_sheet.bridge_charge,confirm_sheet.amerce_cost,sum(confirm_extra_cost.cost) as extra_cost,
  			confirm_sheet.remark,confirm_sheet.loading_status ';
		$criteria->join='LEFT JOIN  confirm_goods_info ON confirm_sheet.id=confirm_goods_info.fk_confirm_id and confirm_goods_info.state=0
  			 LEFT JOIN  confirm_extra_cost ON confirm_sheet.id=confirm_extra_cost.fk_confirm_id and confirm_extra_cost.state=0 ';
		$criteria->group='confirm_sheet.id';
		$criteria->addCondition('confirm_sheet.state=0');
		$blur=true;
		if(Yii::app()->getRequest()->isPostRequest){
			if(isset($_POST['keys'])){//页面传来的搜索关键字数组
				$_GET['keys']=$_POST['keys'];
			}
		}
		if(isset($_GET['keys'])){
			if(isset($_GET['keys']['blur'])&&$_GET['keys']['blur']=='0') $blur=false;
			foreach($_GET['keys'] as $key=>$val){
				if(trim($val)=='')
					unset($_GET['keys'][$key]);
				else if($key=='driver_name'){//司机查询
					if(!$blur)
						$criteria->addCondition("confirm_sheet.driver1_name='{$val}' or confirm_sheet.driver2_name='{$val}'");
					else{
						$criteria->addCondition("confirm_sheet.driver1_name like '%{$val}%' or confirm_sheet.driver2_name like '%{$val}%'");
					}	
				}	
				else if($key=='s_date'){//时间查询
					$criteria->addCondition("DATE_FORMAT(confirm_sheet.appointment_time,'%Y-%m')='{$val}'");
				}
				else if($key=='amount'){//时间查询
					if($val==0)
						$criteria->addCondition("confirm_sheet.total_amount=0");
					else
						$criteria->addCondition("confirm_sheet.total_amount!=0");	
				}
				else if($key!='blur'){//按照模糊或精确查询
					$blur?$criteria->addSearchCondition($key,$val):$criteria->addCondition("{$key}='{$val}'");
				}
			}
		}
		
		if(isset($_GET['excel']))//输出excel
			$this->toSingleTruckExamExcel($criteria);
		else	//转入查询/修改页面
			$this->toSingleTruckExamPage($criteria);
		
	}
	
	public function toSingleTruckExamPage($criteria){
		$model=Single_truck_exam::model();
		$command=$model->getCommandBuilder();//因group by 查询准确记录数量
		$sql="select count(t.id) from( select confirm_sheet.id FROM confirm_sheet";
		$sql=$command->applyJoin($sql,$criteria->join);
		$sql=$command->applyCondition($sql,$criteria->condition);
		$sql=$command->applyGroup($sql,$criteria->group).") as t";//echo $sql;
		$pages=new CPagination($model->countBySql($sql,$criteria->params));
		
		$pages->pageSize=Yii::app()->params['page_size']['single_truck_exam'];
		$pages->applyLimit($criteria);
		
		$sort=new CSort('Single_truck_exam');
		$sort->attributes=array_merge($model->attributeNames(),array('extra_cost','loading_place','pod_place','consignor_name','driver_name'));
		$sort->applyOrder($criteria);
		
		$models=$model->findAll($criteria);
		//如果最后一页加合计
		if(($pages->getCurrentPage()+1)==$pages->getPageCount()){
			$attr_names=$model->excelAttributes();
			$select='confirm_sheet.total_amount,confirm_sheet.return_paper,confirm_sheet.driver_salary,confirm_sheet.gas_fee,
  					confirm_sheet.bridge_charge,confirm_sheet.amerce_cost,sum(confirm_extra_cost.cost) as extra_cost';
			$sql='select "&nbsp;&nbsp;&nbsp;&nbsp;合&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;计" as confirm_no';
			foreach($attr_names as $attr=>$val){
				if(isset($val['sum']))
					$sql.=",sum($attr) as $attr";
			}
			$sql=$sql." from (select $select ";
			$sql.=' from confirm_sheet ';
			$sql=$command->applyJoin($sql,$criteria->join);
			$sql=$command->applyCondition($sql,$criteria->condition);
			$sql=$command->applyGroup($sql,$criteria->group).") as t";//echo $sql;
			
			$sum_model=$model->findBySql($sql,$criteria->params);
			$models[]=$sum_model;
		}
		$this->render('single_truck_exam',array(
			'models'=>$models,
			'pages'=>$pages,
			'sort'=>$sort,
		));
	}
	
	public function toSingleTruckExamExcel($criteria){
		$model=Single_truck_exam::model();
		$sort=new CSort('Single_truck_exam');
		$sort->attributes=array_merge($model->attributeNames(),array('extra_cost','loading_place','pod_place','consignor_name','driver_name'));
		$sort->applyOrder($criteria);
		$models=$model->findAll($criteria);

		$atrr_names=$model->attributeLabels();
		$excel_names=$model->excelAttributes();$end_cell=end($excel_names);
		$tab_name='单车考核 ';
		$tab_name.=(isset($_GET['keys']['s_date'])?$_GET['keys']['s_date']:'');
		$tab_name=trim($tab_name);	
		
		require_once './protected/extensions/excel/PHPExcel.php';
		require_once './protected/extensions/excel/PHPExcel/IOFactory.php';
		$objPHPExcel = new PHPExcel();
		$objPHPExcel->setActiveSheetIndex(0)->setTitle($tab_name);
		$objPHPExcel->getActiveSheet()->mergeCells('A1:'.$end_cell[0].'1');
		$objPHPExcel->getActiveSheet()->setCellValue('A1',$tab_name);
		$objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setSize(15);
		$objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setHorizontal('center');
		$objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(5);
		$objPHPExcel->getActiveSheet()->setCellValue('A2','序号');
		$objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(5);
		$objPHPExcel->getActiveSheet()->freezePane('A3');//标题栏 冻结窗口
		foreach($excel_names as $attr=>$val){//创建标题
			$objPHPExcel->getActiveSheet()->setCellValue($val[0].'2',$atrr_names[$attr]);
			$objPHPExcel->getActiveSheet()->getColumnDimension($val[0])->setWidth($val[1]);
		}
		$row_index=3;//行索引 数据从3开始 例如A从A3开始添加数据
		foreach($models as $n=>$model){
			$objPHPExcel->getActiveSheet()->setCellValue('A'.($n+$row_index),$n+1);
			foreach($model->excelAttributes() as $attr=>$val){//双循环，根据model内设置进行数据导出
				$value=$model->$attr;
				if(isset($val[2]))//值->字符串 转换
					$value=$model->$val[2]();
				$objPHPExcel->getActiveSheet()->setCellValue($val[0].($n+$row_index),$value);
			}
		}
		if(!empty($models)){//最后一行后 合计
			$objPHPExcel->getActiveSheet()->setCellValue('A'.($n+$row_index+1),$n+2);
			$objPHPExcel->getActiveSheet()->setCellValue('B'.($n+$row_index+1),'合      计');
			foreach($excel_names as $attr=>$val){
				if(isset($val['sum'])&&$val['sum']){
					$sum="=sum($val[0]$row_index:$val[0]".($row_index+$n).")";//例：=sum(E3:E7)
					$objPHPExcel->getActiveSheet()->setCellValue($val[0].($n+$row_index+1),$sum);
				}
			}
		}
		header('Content-Type: application/vnd.ms-excel');
		header('Content-Disposition: attachment;filename='.iconv("utf-8", "gb2312", $tab_name.'.xls'));
		header('Cache-Control: max-age=0');
		$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
		$objWriter->save('php://output'); 
	}
	
	public function actionMerit_pay(){//司机绩效汇总
		$criteria=new CDbCriteria();
		$criteria->select=" plate_no,driver_name,type_name, profit,merit_pay_kind,merit_pay,merit_pay_sort,
							CONCAT(FORMAT(merit_pay_ratio*100,0),'%') as merit_pay_ratio,actual_salary,
							date_format(stat_date,'%Y-%m') as stat_date";
		$criteria->addCondition('state=0');
		$blur=true;
		if(Yii::app()->getRequest()->isPostRequest){
			if(isset($_POST['keys'])){//页面传来的搜索关键字数组
				$_GET['keys']=$_POST['keys'];
			}
		}
		if(isset($_GET['keys'])){
			if(isset($_GET['keys']['blur'])&&$_GET['keys']['blur']=='0') $blur=false;
			foreach($_GET['keys'] as $key=>$val){
				if(trim($val)=='')
					unset($_GET['keys'][$key]);
				else if($key=='s_date'){//时间查询
					$criteria->addCondition("stat_date='{$val}-01'");
				}
				else if($key!='blur'){//按照模糊或精确查询
					$blur?$criteria->addSearchCondition($key,$val):$criteria->addCondition("{$key}='{$val}'");
				}
			}
		}
		
		if(isset($_GET['excel']))//输出excel
			$this->toMeritPayExcel($criteria);
		else	//转入查询/修改页面
			$this->toMeritPayPage($criteria);
		
	}
	
	public function toMeritPayPage($criteria){
		$model=Merit_pay_collection::model();
		$pages=new CPagination($model->count($criteria));
		$pages->pageSize=Yii::app()->params['page_size']['merit_pay_collection'];
		$pages->applyLimit($criteria);
		
		$sort=new CSort('Merit_pay_collection');
		$sort->defaultOrder='plate_no desc';
		$sort->applyOrder($criteria);
		$models=$model->findAll($criteria);
		//如果最后一页加合计
		if(($pages->getCurrentPage()+1)==$pages->getPageCount()){
			$attr_names=$model->excelAttributes();
			$sql="'&nbsp;&nbsp;&nbsp;&nbsp;合&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;计' as plate_no";
			foreach($attr_names as $attr=>$val){
				if(isset($val['sum']))
					$sql.=",sum($attr) as $attr";
					
			}
			$criteria->select=$sql;
			$criteria->order='';
			$criteria->limit=1;$criteria->offset=-1;
			$sum_model=$model->find($criteria);
			$models[]=$sum_model;
		}
		$this->render('merit_pay',array(
			'models'=>$models,
			'pages'=>$pages,
			'sort'=>$sort,
		));
	}
	
	public function toMeritPayExcel($criteria){
		$model=Merit_pay_collection::model();
		$sort=new CSort('Merit_pay_collection');
		$sort->defaultOrder='plate_no desc';
		$sort->applyOrder($criteria);
		$models=$model->findAll($criteria);

		$atrr_names=$model->attributeLabels();
		$excel_names=$model->excelAttributes();$end_cell=end($excel_names);
		$tab_name='司机绩效汇总 ';
		$tab_name.=(isset($_GET['keys']['s_date'])?$_GET['keys']['s_date']:'');
		$tab_name=trim($tab_name);	
		
		require_once './protected/extensions/excel/PHPExcel.php';
		require_once './protected/extensions/excel/PHPExcel/IOFactory.php';
		$objPHPExcel = new PHPExcel();
		$objPHPExcel->setActiveSheetIndex(0)->setTitle($tab_name);
		$objPHPExcel->getActiveSheet()->mergeCells('A1:'.$end_cell[0].'1');
		$objPHPExcel->getActiveSheet()->setCellValue('A1',$tab_name);
		$objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setSize(15);
		$objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setHorizontal('center');
		$objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(5);
		$objPHPExcel->getActiveSheet()->setCellValue('A2','序号');
		$objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(5);
		$objPHPExcel->getActiveSheet()->freezePane('A3');//标题栏 冻结窗口
		foreach($excel_names as $attr=>$val){//创建标题
			$objPHPExcel->getActiveSheet()->setCellValue($val[0].'2',$atrr_names[$attr]);
			$objPHPExcel->getActiveSheet()->getColumnDimension($val[0])->setWidth($val[1]);
		}
		$row_index=3;//行索引 数据从3开始 例如A从A3开始添加数据
		foreach($models as $n=>$model){
			$objPHPExcel->getActiveSheet()->setCellValue('A'.($n+$row_index),$n+1);
			foreach($model->excelAttributes() as $attr=>$val){//双循环，根据model内设置进行数据导出
				$value=$model->$attr;
				if(isset($val[2]))//值->字符串 转换
					$value=$model->$val[2]();
				$objPHPExcel->getActiveSheet()->setCellValue($val[0].($n+$row_index),$value);
			}
		}
		if(!empty($models)){//最后一行后 合计
			$objPHPExcel->getActiveSheet()->setCellValue('A'.($n+$row_index+1),$n+2);
			$objPHPExcel->getActiveSheet()->setCellValue('B'.($n+$row_index+1),'合      计');
			foreach($excel_names as $attr=>$val){
				if(isset($val['sum'])&&$val['sum']){
					$sum="=sum($val[0]$row_index:$val[0]".($row_index+$n).")";//例：=sum(E3:E7)
					$objPHPExcel->getActiveSheet()->setCellValue($val[0].($n+$row_index+1),$sum);
				}
			}
		}
		header('Content-Type: application/vnd.ms-excel');
		header('Content-Disposition: attachment;filename='.iconv("utf-8", "gb2312", $tab_name.'.xls'));
		header('Cache-Control: max-age=0');
		$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
		$objWriter->save('php://output'); 
	}
	
	public function actionGas_coll(){//油耗统计汇总
		$criteria=new CDbCriteria();
		$criteria->select="DATE_FORMAT(stat_date,'%Y-%m') as stat_date,incentive_amount,ideal_gas_fee,heavy_fuel,light_fuel,
		empty_fuel,front_fuel,heavy_driving_mileage,light_driving_mileage,empty_driving_mileage, front_driving_mileage,
		total_mileage,end_odometer_no,early_odometer_no,actual_gas_fee,fuel,type_name,main_driver_name,plate_no";
		$criteria->addCondition('state=0');
		$blur=true;
		if(Yii::app()->getRequest()->isPostRequest){
			if(isset($_POST['keys'])){//页面传来的搜索关键字数组
				$_GET['keys']=$_POST['keys'];
			}
		}
		if(isset($_GET['keys'])){
			if(isset($_GET['keys']['blur'])&&$_GET['keys']['blur']=='0') $blur=false;
			foreach($_GET['keys'] as $key=>$val){
				if(trim($val)=='')
					unset($_GET['keys'][$key]);
				else if($key=='s_date'){//时间查询
					$criteria->addCondition("stat_date='{$val}-01'");
				}
				else if($key!='blur'){//按照模糊或精确查询
					$blur?$criteria->addSearchCondition($key,$val):$criteria->addCondition("{$key}='{$val}'");
				}
			}
		}
		
		if(isset($_GET['excel']))//输出excel
			$this->toGasCollExcel($criteria);
		else	//转入查询/修改页面
			$this->toGasCollPage($criteria);
		
	}
	
	public function toGasCollPage($criteria){
		$model=Gas_fee_stat_collection::model();
		$pages=new CPagination($model->count($criteria));
		$pages->pageSize=Yii::app()->params['page_size']['gas_fee_stat_collection'];
		$pages->applyLimit($criteria);
		
		$sort=new CSort('Gas_fee_stat_collection');
		$sort->defaultOrder='plate_no';
		$sort->applyOrder($criteria);
		$models=$model->findAll($criteria);
		
		//如果最后一页加合计
		if(($pages->getCurrentPage()+1)==$pages->getPageCount()){
			$attr_names=$model->excelAttributes();
			$sql="'&nbsp;&nbsp;&nbsp;&nbsp;合&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;计' as plate_no";
			foreach($attr_names as $attr=>$val){
				if(isset($val['sum']))
					$sql.=",sum($attr) as $attr";
					
			}
			$criteria->select=$sql;
			$criteria->order='';
			$criteria->limit=1;$criteria->offset=-1;
			$sum_model=$model->find($criteria);
			$models[]=$sum_model;
		}
		$this->render('gas_fee_coll',array(
			'models'=>$models,
			'pages'=>$pages,
			'sort'=>$sort,
		));
	}
	
	public function toGasCollExcel($criteria){
		$model=Gas_fee_stat_collection::model();
		$sort=new CSort('Gas_fee_stat_collection');
		$sort->defaultOrder='plate_no';
		$sort->applyOrder($criteria);
		$models=$model->findAll($criteria);

		$atrr_names=$model->attributeLabels();
		$excel_names=$model->excelAttributes();$end_cell=end($excel_names);
		$tab_name='油耗统计汇总 ';
		$tab_name.=(isset($_GET['keys']['s_date'])?$_GET['keys']['s_date']:'');
		$tab_name=trim($tab_name);	
		
		require_once './protected/extensions/excel/PHPExcel.php';
		require_once './protected/extensions/excel/PHPExcel/IOFactory.php';
		$objPHPExcel = new PHPExcel();
		$objPHPExcel->setActiveSheetIndex(0)->setTitle($tab_name);
		$objPHPExcel->getActiveSheet()->mergeCells('A1:'.$end_cell[0].'1');
		$objPHPExcel->getActiveSheet()->setCellValue('A1',$tab_name);
		$objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setSize(15);
		$objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setHorizontal('center');
		$objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(5);
		$objPHPExcel->getActiveSheet()->setCellValue('A2','序号');
		$objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(5);
		$objPHPExcel->getActiveSheet()->freezePane('A3');//标题栏 冻结窗口
		foreach($excel_names as $attr=>$val){//创建标题
			$objPHPExcel->getActiveSheet()->setCellValue($val[0].'2',$atrr_names[$attr]);
			$objPHPExcel->getActiveSheet()->getColumnDimension($val[0])->setWidth($val[1]);
		}
		$row_index=3;//行索引 数据从3开始 例如A从A3开始添加数据
		foreach($models as $n=>$model){
			$objPHPExcel->getActiveSheet()->setCellValue('A'.($n+$row_index),$n+1);
			foreach($model->excelAttributes() as $attr=>$val){//双循环，根据model内设置进行数据导出
				$value=$model->$attr;
				if(isset($val[2]))//值->字符串 转换
					$value=$model->$val[2]();
				$objPHPExcel->getActiveSheet()->setCellValue($val[0].($n+$row_index),$value);
			}
		}
		if(!empty($models)){//最后一行后 合计
			$objPHPExcel->getActiveSheet()->setCellValue('A'.($n+$row_index+1),$n+2);
			$objPHPExcel->getActiveSheet()->setCellValue('B'.($n+$row_index+1),'合      计');
			foreach($excel_names as $attr=>$val){
				if(isset($val['sum'])&&$val['sum']){
					$sum="=sum($val[0]$row_index:$val[0]".($row_index+$n).")";//例：=sum(E3:E7)
					$objPHPExcel->getActiveSheet()->setCellValue($val[0].($n+$row_index+1),$sum);
				}
			}
		}
		header('Content-Type: application/vnd.ms-excel');
		header('Content-Disposition: attachment;filename='.iconv("utf-8", "gb2312", $tab_name.'.xls'));
		header('Cache-Control: max-age=0');
		$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
		$objWriter->save('php://output');  
	}
	
	public function actionGas_incentive(){//油耗奖惩
		$criteria=new CDbCriteria();
		$criteria->select="DATE_FORMAT(stat_date,'%Y-%m') as stat_date,incentive_amount,ideal_gas_fee,heavy_fuel,light_fuel,
		empty_fuel,front_fuel,heavy_driving_mileage,light_driving_mileage,empty_driving_mileage,front_driving_mileage,
		total_mileage,actual_gas_fee,fuel,type_name,driver_name,plate_no";
		$criteria->addCondition('state=0');
		$blur=true;
		if(Yii::app()->getRequest()->isPostRequest){
			if(isset($_POST['keys'])){//页面传来的搜索关键字数组
				$_GET['keys']=$_POST['keys'];
			}
		}
		if(isset($_GET['keys'])){
			if(isset($_GET['keys']['blur'])&&$_GET['keys']['blur']=='0') $blur=false;
			foreach($_GET['keys'] as $key=>$val){
				if(trim($val)=='')
					unset($_GET['keys'][$key]);
				else if($key=='s_date'){//时间查询
					$criteria->addCondition("stat_date='{$val}-01'");
				}
				else if($key!='blur'){//按照模糊或精确查询
					$blur?$criteria->addSearchCondition($key,$val):$criteria->addCondition("{$key}='{$val}'");
				}
			}
		}
		
		if(isset($_GET['excel']))//输出excel
			$this->toGasIncentiveExcel($criteria);
		else	//转入查询/修改页面
			$this->toGasIncentivePage($criteria);
		
	}
	
	public function toGasIncentivePage($criteria){
		$model=Gas_fee_incentive_stat::model();
		$pages=new CPagination($model->count($criteria));
		$pages->pageSize=Yii::app()->params['page_size']['gas_fee_incentive_stat'];
		$pages->applyLimit($criteria);
		
		$sort=new CSort('Gas_fee_incentive_stat');
		$sort->defaultOrder='plate_no';
		$sort->applyOrder($criteria);
		$models=$model->findAll($criteria);
		//如果最后一页加合计
		if(($pages->getCurrentPage()+1)==$pages->getPageCount()){
			$attr_names=$model->excelAttributes();
			$sql="'&nbsp;&nbsp;&nbsp;&nbsp;合&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;计' as plate_no";
			foreach($attr_names as $attr=>$val){
				if(isset($val['sum']))
					$sql.=",sum($attr) as $attr";
					
			}
			$criteria->select=$sql;
			$criteria->order='';
			$criteria->limit=1;$criteria->offset=-1;
			$sum_model=$model->find($criteria);
			$models[]=$sum_model;
		}
		$this->render('gas_incentive',array(
			'models'=>$models,
			'pages'=>$pages,
			'sort'=>$sort,
		));
	}
	
	public function toGasIncentiveExcel($criteria){
		$model=Gas_fee_incentive_stat::model();
		$sort=new CSort('Gas_fee_incentive_stat');
		$sort->defaultOrder='plate_no';
		$sort->applyOrder($criteria);
		$models=$model->findAll($criteria);

		$atrr_names=$model->attributeLabels();
		$excel_names=$model->excelAttributes();$end_cell=end($excel_names);
		$tab_name='油耗奖惩 ';
		$tab_name.=(isset($_GET['keys']['s_date'])?$_GET['keys']['s_date']:'');
		$tab_name=trim($tab_name);	
		
		require_once './protected/extensions/excel/PHPExcel.php';
		require_once './protected/extensions/excel/PHPExcel/IOFactory.php';
		$objPHPExcel = new PHPExcel();
		$objPHPExcel->setActiveSheetIndex(0)->setTitle($tab_name);
		$objPHPExcel->getActiveSheet()->mergeCells('A1:'.$end_cell[0].'1');
		$objPHPExcel->getActiveSheet()->setCellValue('A1',$tab_name);
		$objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setSize(15);
		$objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setHorizontal('center');
		$objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(5);
		$objPHPExcel->getActiveSheet()->setCellValue('A2','序号');
		$objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(5);
		$objPHPExcel->getActiveSheet()->freezePane('A3');//标题栏 冻结窗口
		foreach($excel_names as $attr=>$val){//创建标题
			$objPHPExcel->getActiveSheet()->setCellValue($val[0].'2',$atrr_names[$attr]);
			$objPHPExcel->getActiveSheet()->getColumnDimension($val[0])->setWidth($val[1]);
		}
		$row_index=3;//行索引 数据从3开始 例如A从A3开始添加数据
		foreach($models as $n=>$model){
			$objPHPExcel->getActiveSheet()->setCellValue('A'.($n+$row_index),$n+1);
			foreach($model->excelAttributes() as $attr=>$val){//双循环，根据model内设置进行数据导出
				$value=$model->$attr;
				if(isset($val[2]))//值->字符串 转换
					$value=$model->$val[2]();
				$objPHPExcel->getActiveSheet()->setCellValue($val[0].($n+$row_index),$value);
			}
		}
		if(!empty($models)){//最后一行后 合计
			$objPHPExcel->getActiveSheet()->setCellValue('A'.($n+$row_index+1),$n+2);
			$objPHPExcel->getActiveSheet()->setCellValue('B'.($n+$row_index+1),'合      计');
			foreach($excel_names as $attr=>$val){
				if(isset($val['sum'])&&$val['sum']){
					$sum="=sum($val[0]$row_index:$val[0]".($row_index+$n).")";//例：=sum(E3:E7)
					$objPHPExcel->getActiveSheet()->setCellValue($val[0].($n+$row_index+1),$sum);
				}
			}
		}
		header('Content-Type: application/vnd.ms-excel');
		header('Content-Disposition: attachment;filename='.iconv("utf-8", "gb2312", $tab_name.'.xls'));
		header('Cache-Control: max-age=0');
		$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
		$objWriter->save('php://output'); 
	}
	
	public function actionTime_statistics(){//调用统计存储过程
		$form =new StatisticsFrom;
		if(isset($_POST['StatisticsFrom'])){
			$form->attributes=$_POST['StatisticsFrom'];
			if($form->validate()){
				$model=new Manual_stat_log;
				$command=$model->getDbConnection();
				$tran=$command->beginTransaction();
				try{
					$command->createCommand("call daily_run('{$form->time}')")->execute();
					$model->name=Yii::app()->session['true_name'];
					$model->stat_date=$form->time;
					$model->create_time=date('Y-m-d G:i:s');
					$model->save();
					$tran->commit();
				}catch(Exception $e){
					$tran->rollBack();
					$this->redirect(array('time_statistics','mess'=>'操作失败!'));
				}
				$this->redirect(array('time_statistics','mess'=>"操作成功!"));
			}
		}
		$this->render('time_statistics',array('form'=>$form));
	}
	
	public function actionManual_stat_log(){
		$criteria=new CDbCriteria();
		$blur=true;
		if(Yii::app()->getRequest()->isPostRequest){
			if(isset($_POST['keys'])){//页面传来的搜索关键字数组
				$_GET['keys']=$_POST['keys'];
			}
		}
		if(isset($_GET['keys'])){
			if(isset($_GET['keys']['blur'])&&$_GET['keys']['blur']=='0') $blur=false;
			foreach($_GET['keys'] as $key=>$val){
				if(trim($val)=='')
					unset($_GET['keys'][$key]);
				else if($key=='s_date'){//时间查询
					$criteria->addCondition("stat_date='{$val}-01'");
				}
				else if($key!='blur'){//按照模糊或精确查询
					$blur?$criteria->addSearchCondition($key,$val):$criteria->addCondition("{$key}='{$val}'");
				}
			}
		}
		$model=Manual_stat_log::model();
		$pages=new CPagination($model->count($criteria));
		$pages->pageSize=Yii::app()->params['page_size']['manual_stat_log'];
		$pages->applyLimit($criteria);
		
		$sort=new CSort('Manual_stat_log');
		$sort->applyOrder($criteria);
		$models=$model->findAll($criteria);
		$this->render('manual_stat_log',array(
			'models'=>$models,
			'pages'=>$pages,
			'sort'=>$sort,
		));
	}
}
